About Dataset:
- Employee_ID : Contains Unique_id for employee.
- Question_ID : Contains 3 question mentioned as 101(personal value), 102(Curent cultural values), 103(Desired cultural Values).
- Selected values : it contain set of Option values selected for each question.
# importing all required packages
import pandas as pd # for pandas
import numpy as np #for array
import matplotlib as plt # for visualization
import matplotlib.pyplot as plt # for visualization
import seaborn as sns # for visualization
import bokeh as bk # for viz.
import missingno as mn # for table visualization
%matplotlib inline
import plotly.express as px
import plotly.graph_objects as go # Visualization
Importing the dataset
df = pd.read_csv('Culturelytics_Task-data analytics.csv') # loading datafranme
df
| Employee_ID | Question_ID | Selected_Value | Value_Order | |
|---|---|---|---|---|
| 0 | 2385 | 101 | Accountability | 1 |
| 1 | 2385 | 101 | Achievement | 2 |
| 2 | 2385 | 101 | Commitment | 3 |
| 3 | 2385 | 101 | Cost Reduction | 4 |
| 4 | 2385 | 101 | Efficiency | 5 |
| ... | ... | ... | ... | ... |
| 72145 | 1092 | 103 | Cost Reduction | 6 |
| 72146 | 1092 | 103 | Financial Stability | 7 |
| 72147 | 1092 | 103 | Global Thinking | 8 |
| 72148 | 1092 | 103 | Influence | 9 |
| 72149 | 1092 | 103 | Leadership Development | 10 |
72150 rows × 4 columns
df.describe() #describing the data
| Employee_ID | Question_ID | Value_Order | |
|---|---|---|---|
| count | 72150.000000 | 72150.000000 | 72150.000000 |
| mean | 1944.217879 | 102.000000 | 5.500000 |
| std | 1049.797010 | 0.816502 | 2.872301 |
| min | 147.000000 | 101.000000 | 1.000000 |
| 25% | 1027.000000 | 101.000000 | 3.000000 |
| 50% | 1938.000000 | 102.000000 | 5.500000 |
| 75% | 2852.000000 | 103.000000 | 8.000000 |
| max | 3747.000000 | 103.000000 | 10.000000 |
df.info() # information of data
<class 'pandas.core.frame.DataFrame'> RangeIndex: 72150 entries, 0 to 72149 Data columns (total 4 columns): # Column Non-Null Count Dtype --- ------ -------------- ----- 0 Employee_ID 72150 non-null int64 1 Question_ID 72150 non-null int64 2 Selected_Value 72150 non-null object 3 Value_Order 72150 non-null int64 dtypes: int64(3), object(1) memory usage: 2.2+ MB
df.isna().sum() # sum of null values
Employee_ID 0 Question_ID 0 Selected_Value 0 Value_Order 0 dtype: int64
# Calculate the correlation matrix
corr_matrix = df.corr()
plt.figure(figsize=(10, 8))
sns.heatmap(corr_matrix, annot=True, cmap='coolwarm')
plt.title('Correlation Heatmap')
plt.show()
it indicates there is no co-relation in the data there are 4 different columns
The Dataset have 3 question as following
- personal values: Quention_ID 101, where the employee selects his personal quality in order in Value_Order column and as based on his selection we get Order_Value such that the first selected value have higher weightage.
- Current cultural values: Question_ID 102, where the Employee select the values based on the current culture of the organisation in order in Value_Order column and as based on his selection we get Order_Value such that the first selected value have higher weightage.
- Desired cultural values: Question_ID 103, where the Employee select the values based on the required uplifments and the required cultural value that are to be practiced for the growth in order in Value_Order column and as based on his selection we get Order_Value such that the first selected value have higher weightage.
Employee Assessment
Current Organisational Culture
Desired Organisational Culture For Future
Result Board
# selecting Employee
employee_id = int(input("Enter the Employee ID: "))
# Few examples of Employee Id in dataset are selec in these
# 3200, 242, 3606, 1783, 2230, 528, 2098 , 900, 1547 ,303 , 323, 3583, 1718, 268,3547, 1703,235,1289,3723,3350
Enter the Employee ID: 2814
import plotly.graph_objects as go
# defing qualities
assessments = {
'Leadership Assessment': ['Accountability', 'Achievement', 'Commitment', 'Integrity', 'Transparency', 'Vision', 'Leadership Development', 'Team Work'],
'Team Collaboration Assessment': ['Teamwork', 'Trust', 'Conflict Resolution', 'Inclusiveness', 'Coaching/Mentoring', 'Communication', 'Reliability'],
'Employee Engagement Assessment': ['Empowerment', 'Recognition', 'Information Sharing', 'Well-Being', 'Sustainability', 'Community Involvement', 'Employee Engagement'],
'Innovation Mindset Assessment': ['Creativity', 'Continuous Improvement', 'Long-Term Perspective', 'Open Communication', 'Risk-Taking', 'Change Averse', 'Decisiveness']
}
def calculate_overall_score(employee_id, qualities):
filtered_data = df[(df['Employee_ID'] == employee_id) & (df['Selected_Value'].isin(qualities))]
total_points = len(filtered_data) * 10
points_scored = filtered_data.groupby('Selected_Value')['Value_Order'].sum()
percentage_scores = (points_scored / total_points) * 100
overall_score = percentage_scores.sum()
return overall_score
# list to store overall scores
overall_scores = []
# assessments for the given Employee ID
for assessment, qualities in assessments.items():
overall_score = calculate_overall_score(employee_id, qualities)
overall_scores.append(overall_score)
# plotly radial chart
fig = go.Figure()
# radial chart traces
fig.add_trace(go.Scatterpolar(
r=overall_scores,
theta=list(assessments.keys()),
fill='toself',
name='Score',
marker=dict(color='purple')
))
# layout properties
fig.update_layout(
polar=dict(
radialaxis=dict(
visible=True,
range=[0, 100]
)
),
showlegend=False
)
fig.show()
# Ploting The Selected employees Leadership Skill
qualities = ['Accountability', 'Achievement', 'Commitment', 'Integrity', 'Transparency', 'Vision', 'Leadership Development', 'Team Work']
# Filter the data
filtered_data = df[(df['Employee_ID'] == employee_id) & (df['Selected_Value'].isin(qualities))]
total_points = len(filtered_data) * 10
points_scored = filtered_data.groupby('Selected_Value')['Value_Order'].sum().sum()
leadership_score = 100 - ((points_scored / total_points) * 100)
remaining_percentage = 100 - leadership_score
# donut chart
fig = go.Figure(go.Pie(
labels=['Leadership Score', 'Remaining'],
values=[leadership_score, remaining_percentage],
hole=0.6,
marker=dict(colors=['#FFB6C1', '#F8F8FF']),
hoverinfo='label+percent',
textinfo='label',
showlegend=False
))
fig.update_layout(
title='Leadership Score',
annotations=[dict(text=f"{leadership_score:.2f}%", x=0.5, y=0.5, font_size=30, showarrow=False)],
)
fig.show()
# Plot for Team Collaboration Assessment
qualities = ['Teamwork', 'Trust', 'Conflict Resolution', 'Inclusiveness', 'Coaching/Mentoring', 'Communication', 'Reliability']
# Filter the data
filtered_data = df[(df['Employee_ID'] == employee_id) & (df['Selected_Value'].isin(qualities))]
total_points = len(filtered_data) * 10
points_scored = filtered_data.groupby('Selected_Value')['Value_Order'].sum().sum()
team_collaboration_score = 100 - ((points_scored / total_points) * 100)
remaining_percentage = 100 - team_collaboration_score
# donut chart
fig = go.Figure(go.Pie(
labels=['Team Collaboration Score', 'Remaining'],
values=[team_collaboration_score, remaining_percentage],
hole=0.6,
marker=dict(colors=['#FFB6C1', '#F8F8FF']),
hoverinfo='label+percent',
textinfo='label',
showlegend=False
))
# layout properties
fig.update_layout(
title='Team Collaboration Score',
annotations=[dict(text=f"{team_collaboration_score:.2f}%", x=0.5, y=0.5, font_size=30, showarrow=False)],
)
fig.show()
# Plot for Employee Engagement
qualities = ['Empowerment', 'Recognition', 'Information Sharing', 'Well-Being', 'Sustainability', 'Community Involvement', 'Employee Engagement']
# Filter the data
filtered_data = df[(df['Employee_ID'] == employee_id) & (df['Selected_Value'].isin(qualities))]
total_points = len(filtered_data) * 10
points_scored = filtered_data[filtered_data['Selected_Value'] == 'Employee Engagement']['Value_Order'].sum()
employee_engagement_score = 100 -((points_scored / total_points) * 100)
remaining_percentage = 100 - employee_engagement_score
# donut chart
fig = go.Figure(go.Pie(
labels=['Employee Engagement Score', 'Remaining'],
values=[employee_engagement_score, remaining_percentage],
hole=0.6,
marker=dict(colors=['#FFB6C1', '#F8F8FF']),
hoverinfo='label+percent',
textinfo='label',
showlegend=False
))
# layout properties
fig.update_layout(
title='Employee Engagement Score',
annotations=[dict(text=f"{employee_engagement_score:.2f}%", x=0.5, y=0.5, font_size=30, showarrow=False)],
)
fig.show()
#Plot for the Innovation Mindset Assessment
qualities = ['Creativity', 'Continuous Improvement', 'Long-Term Perspective', 'Open Communication', 'Risk-Taking',
'Change Averse', 'Decisiveness']
# Filter the data
filtered_data = df[(df['Employee_ID'] == employee_id) & (df['Selected_Value'].isin(qualities))]
total_points = len(filtered_data) * 10
points_scored = filtered_data.groupby('Selected_Value')['Value_Order'].sum().sum()
innovation_mindset_score = (points_scored / total_points) * 100
remaining_percentage = 100 - innovation_mindset_score
# donut chart
fig = go.Figure(go.Pie(
labels=['Innovation Mindset Score', 'Remaining'],
values=[innovation_mindset_score, remaining_percentage],
hole=0.6,
marker=dict(colors=['#FFB6C1', '#F8F8FF']),
hoverinfo='label+percent',
textinfo='label',
showlegend=False
))
# layout properties
fig.update_layout(
title='Innovation Mindset Score',
annotations=[dict(text=f"{innovation_mindset_score:.2f}%", x=0.5, y=0.5, font_size=30, showarrow=False)],
)
fig.show()
Dashboard 1 : ends
# Overall understanding
assessments = {
'Leadership Assessment': ['Accountability', 'Achievement', 'Commitment', 'Integrity', 'Transparency', 'Vision', 'Leadership Development', 'Team Work'],
'Organizational Culture Assessment': ['Ethics', 'Excellence', 'Continuous Learning', 'Customer Satisfaction', 'Organizational Growth', 'Employee Engagement'],
'Change Management Assessment': ['Open Communication', 'Adaptability', 'Innovation', 'Risk-Taking', 'Influence', 'Ease with Uncertainty', 'Patience', 'Personal Growth'],
'Team Collaboration Assessment': ['Teamwork', 'Trust', 'Conflict Resolution', 'Inclusiveness', 'Coaching/Mentoring', 'Communication', 'Reliability'],
'Employee Engagement Assessment': ['Empowerment', 'Recognition', 'Information Sharing', 'Well-Being', 'Sustainability', 'Community Involvement', 'Employee Engagement'],
'Innovation Mindset Assessment': ['Creativity', 'Continuous Improvement', 'Long-Term Perspective', 'Open Communication', 'Risk-Taking', 'Change Averse', 'Decisiveness']
}
# overall score for each assessment
def calculate_overall_score(employee_id, qualities):
filtered_data = df[(df['Employee_ID'] == employee_id) & (df['Selected_Value'].isin(qualities))]
total_points = len(filtered_data) * 10
points_scored = filtered_data.groupby('Selected_Value')['Value_Order'].sum()
percentage_scores = (points_scored / total_points) * 100
overall_score = percentage_scores.sum()
return overall_score
# list to store overall scores
overall_scores = []
# assessments for the given Employee ID
for assessment, qualities in assessments.items():
overall_score = calculate_overall_score(employee_id, qualities)
overall_scores.append(overall_score)
# radial chart
fig = go.Figure()
# radial chart traces
fig.add_trace(go.Scatterpolar(
r=overall_scores,
theta=list(assessments.keys()),
fill='toself',
name='Score',
marker=dict(color='purple')
))
# layout properties
fig.update_layout(
polar=dict(
radialaxis=dict(
visible=True,
range=[0, 100]
)
),
showlegend=False
)
fig.show()
#Plot for Change Management Assessment
qualities = ['Open Communication', 'Adaptability', 'Innovation', 'Risk-Taking', 'Influence', 'Ease with Uncertainty', 'Patience', 'Personal Growth']
filtered_data = df[(df['Employee_ID'] == employee_id) & (df['Selected_Value'].isin(qualities))]
total_points = len(filtered_data) * 10
points_scored = filtered_data.groupby('Selected_Value')['Value_Order'].sum().sum()
change_management_score = 100 - ((points_scored / total_points) * 100)
remaining_percentage = 100 - change_management_score
# donut chart
fig = go.Figure(go.Pie(
labels=['Change Management Score', 'Remaining'],
values=[change_management_score, remaining_percentage],
hole=0.6,
marker=dict(colors=['#FFB6C1', '#F8F8FF']),
hoverinfo='label+percent',
textinfo='label',
showlegend=False
))
# layout properties
fig.update_layout(
title='Change Management Score',
annotations=[dict(text=f"{change_management_score:.2f}%", x=0.5, y=0.5, font_size=30, showarrow=False)],
)
fig.show()
# Plot for Organizational Culture Assessment
qualities = ['Ethics', 'Excellence', 'Continuous Learning', 'Customer Satisfaction', 'Organizational Growth', 'Employee Engagement']
filtered_data = df[(df['Employee_ID'] == employee_id) & (df['Selected_Value'].isin(qualities))]
total_points = len(filtered_data) * 10
points_scored = filtered_data.groupby('Selected_Value')['Value_Order'].sum().sum()
organizational_culture_score = 100 - ((points_scored / total_points) * 100)
remaining_percentage = 100 - organizational_culture_score
# donut chart
fig = go.Figure(go.Pie(
labels=['Organizational Culture Score', 'Remaining'],
values=[organizational_culture_score, remaining_percentage],
hole=0.6,
marker=dict(colors=['#FFB6C1', '#F8F8FF']),
hoverinfo='label+percent',
textinfo='label',
showlegend=False
))
# layout properties
fig.update_layout(
title='Organizational Culture Score',
annotations=[dict(text=f"{organizational_culture_score:.2f}%", x=0.5, y=0.5, font_size=30, showarrow=False)],
)
fig.show()
# qualities to assess for the Change Management Assessment
qualities = ['Open Communication', 'Adaptability', 'Innovation', 'Risk-Taking', 'Influence', 'Ease with Uncertainty', 'Patience', 'Personal Growth']
filtered_data = df[(df['Employee_ID'] == employee_id) & (df['Selected_Value'].isin(qualities))]
total_points = len(filtered_data) * 10
points_scored = filtered_data.groupby('Selected_Value')['Value_Order'].sum()
percentage_scores = 100 - ((points_scored / total_points) * 100)
# plotly bar chart
fig = go.Figure(go.Bar(
x=percentage_scores.values,
y=percentage_scores.index,
orientation='h',
marker=dict(color='#FFB6C1'),
))
# layout properties
fig.update_layout(
title='Change Management Trait Scores',
xaxis=dict(title='Percentage'),
yaxis=dict(title='Qualities'),
)
fig.show()
# qualities to assess for the Organizational Culture Assessment
qualities = ['Ethics', 'Excellence', 'Continuous Learning', 'Customer Satisfaction', 'Organizational Growth', 'Employee Engagement']
filtered_data = df[(df['Employee_ID'] == employee_id) & (df['Selected_Value'].isin(qualities))]
total_points = len(filtered_data) * 10
points_scored = filtered_data.groupby('Selected_Value')['Value_Order'].sum()
percentage_scores = (points_scored / total_points) * 100
# plotly bar chart
fig = go.Figure(go.Bar(
x=percentage_scores.values,
y=percentage_scores.index,
orientation='h',
marker=dict(color='#FFB6C1'),
))
# layout properties
fig.update_layout(
title='Organizational Culture Trait Scores',
xaxis=dict(title='Percentage'),
yaxis=dict(title='Qualities'),
)
fig.show()
Dashboard2: ends
#creating new column with Value_Order with proper point
value_mapping = {1: 10, 2: 9, 3: 8, 4: 7, 5: 6, 6:5 , 7:4, 8:3 , 9:2, 10:1}
df['New_Value'] = df['Value_Order'].map(value_mapping)
print(df)
Employee_ID Question_ID Selected_Value Value_Order \
0 2385 101 Accountability 1
1 2385 101 Achievement 2
2 2385 101 Commitment 3
3 2385 101 Cost Reduction 4
4 2385 101 Efficiency 5
... ... ... ... ...
72145 1092 103 Cost Reduction 6
72146 1092 103 Financial Stability 7
72147 1092 103 Global Thinking 8
72148 1092 103 Influence 9
72149 1092 103 Leadership Development 10
New_Value
0 10
1 9
2 8
3 7
4 6
... ...
72145 5
72146 4
72147 3
72148 2
72149 1
[72150 rows x 5 columns]
# Desired Changes
assessment_qualities = ['Compassion', 'Financial Stability', 'Customer Satisfaction', 'Global Thinking',
'Hierarchy', 'Job Security', 'Transparency', 'Information Sharing', 'Fairness', 'Cost Reduction']
filtered_data = df[df['Question_ID'] == 103]
filtered_data = filtered_data[filtered_data['Selected_Value'].isin(assessment_qualities)]
# Group the data by Selected_Value and calculate the sum of Value and count of Employee_ID
grouped_data = filtered_data.groupby('Selected_Value').agg({'New_Value': 'sum', 'Employee_ID': 'count'}).reset_index()
# bubble chart
fig = px.scatter(grouped_data, x='Selected_Value', y='New_Value', size='Employee_ID', hover_name='Selected_Value',
title='Bubble Chart for Assessment Question ID 103')
# layout properties
fig.update_layout(xaxis_title='Selected Value', yaxis_title='Value', showlegend=False)
fig.show()
Dashboard3: ends
#Treemap
grouped_data = df.groupby('Selected_Value')['New_Value'].sum().reset_index()
fig = px.treemap(grouped_data, path=['Selected_Value'], values='New_Value')
fig.update_layout(title='Treemap Visualization')
fig.show()
#Barchart
bar_data = df[['Question_ID', 'Selected_Value', 'Value_Order']]
grouped_data = bar_data.groupby(['Question_ID', 'Selected_Value'])['Value_Order'].sum().reset_index()
sorted_data = grouped_data.groupby('Question_ID').apply(lambda x: x.sort_values('Value_Order', ascending=False)).reset_index(drop=True)
fig = go.Figure()
for question_id in sorted_data['Question_ID'].unique():
question_data = sorted_data[sorted_data['Question_ID'] == question_id]
fig.add_trace(go.Bar(x=question_data['Selected_Value'], y=question_data['Value_Order'], name=f'Question ID: {question_id}'))
# layout properties
fig.update_layout(
barmode='group',
title='Sum of Value_Order by Selected_Value (Separated by Question_ID)',
xaxis_title='Selected_Value',
yaxis_title='Sum of Value_Order'
)
fig.show()